# @Time: 2020/07/29 10:50
# @Author: may
# @Desc: 所有证件
import re
import time
import screenshot
from excel import read_excel
from format import to_str, to_list
from selenium.webdriver.common.by import By
from path import get_filepath, clear_old_excel
from data_entry.data_entry import DataEntry
from logger import Logger
from decimal import Decimal


class Certificate(DataEntry):
    """证件"""

    add_btn = (By.LINK_TEXT, '添 加')
    update_btn = (By.XPATH, '//tr[1]//td[2]//div[1]//a[2]//span[1]')
    save_btn = (By.XPATH, "//a[@class='saveBlue rightHandel']")
    detail_btn = (By.XPATH, '//tr[1]//td[2]//div[1]//a[1]//span[1]')
    export_btn = (By.LINK_TEXT, '导出excel')
    export_old = (By.LINK_TEXT, '导出')

    def export(self, option, certificate_name):
        """导出excel"""

        # 选择证件
        self.click_data_entry()
        self.select_credentials(option)
        # 导出前先删除之前导出的文件
        clear_old_excel(certificate_name)
        # 点击导出按钮
        if option == 3 or option == 9:
            self.click_export_btn('old')
        else:
            self.click_export_btn()
        time.sleep(2)
        path = get_filepath(certificate_name)
        if path:
            data_excel = read_excel(path)
        else:
            data_excel = None
        project_id = self.project_id
        data = {1: self.get_info_a(project_id), 2: self.get_info_b(project_id),
                3: self.find_all(project_id)}
        data_db = data[option]
        if data_db:
            data_format = to_list(to_str(data_db))
        else:
            data_format = None
        msg = 'failure'
        if data_excel and data_format and data_excel == data_format:
            self.logger.info(f'[{certificate_name}]导出成功')
            msg = 'success'
        else:
            self.logger.error(f'[{certificate_name}]导出失败')
            self.logger.error('excel数据：' + str(data_excel))
            self.logger.error('数据库数据：' + str(data_format))
        return msg

    def click_delete_btn(self):
        """通用删除按钮"""

        self.click((By.XPATH, '//tr[1]//td[2]//div[1]//a[3]//span[1]'))

    def delete(self, option, certificate_name):
        """删除证件"""

        # 点击删除按钮
        if option == 3:
            self.click_btn_of_certificate(option, (By.XPATH, '//tr[1]//td[8]//div[1]//a[3]//span[1]'))
        elif option == 10:
            self.click_btn_of_certificate(option, (By.XPATH, '//tr[1]//td[7]//div[1]//a[3]//span[1]'))
        else:
            self.click_btn_of_certificate(option, (By.XPATH, '//tr[1]//td[2]//div[1]//a[3]//span[1]'))
        self.click((By.LINK_TEXT, '确定'))
        boolean = self.text_to_be_present_in_text((By.XPATH, '(//div[@class="layui-layer-content"])[last()]'), '删除成功！')
        page_count = self.get_text((By.XPATH, "//span[@class='layui-laypage-count']"))
        count = re.sub(r'\D', '', page_count)
        project_id = self.project_id
        count_database = str(self.get_contract_count(option, project_id))
        if boolean and count == count_database:
            self.logger.info(f'[{certificate_name}]删除成功')
            return 'success'
        else:
            self.logger.error(f'[{certificate_name}]删除失败')
            self.logger.error(f'[{certificate_name}]-前台数量：' + count)
            self.logger.error(f'[{certificate_name}]-后台数量' + count_database)
            return 'failure'

    def get_tr_of_level_2(self):
        tbody = self.find_element((By.XPATH, "//div[@class='layui-table-body layui-table-main']//tbody"))
        # 获取tbody中的所有行
        return tbody.find_elements(By.TAG_NAME, 'tr')

    def get_count_of_annex(self, certificate_type, project_id, certificate_id, sql):
        """根据证件类型，项目id，批文编号获取该批文附件数量"""

        conn = self.get_conn()
        cursor = conn.cursor()
        sql = f'SELECT count(*) FROM DJzstp t WHERE t.Zslx = ? AND t.ZsGuid = ({sql})'
        with conn:
            cursor.execute(sql, certificate_type, project_id, certificate_id)
            row = cursor.fetchone()[0]
        return row

    def check_detail_data(self, certificate_name, info, info_db, count_annex, count_annex_db):
        """校验各证件详情数据"""

        msg = 'failure'
        if info == info_db and count_annex == count_annex_db:
            msg = 'success'
            self.logger.info(f'[{certificate_name}]详情数据正常')
        else:
            self.logger.error(f'[{certificate_name}]详情数据异常')
            self.logger.error(f'前台证件信息：{info}')
            self.logger.error(f'后台证件信息：{info_db}')
            self.logger.error(f'前台附件数量：{count_annex}')
            self.logger.error(f'后台附件数量：{count_annex_db}')
        return msg

    def get_info_of_detail(self, stop, option=None):
        """
        根据字段个数获取详情每个字段

        :param stop: 字段个数
        :param option: 证件类型
        :return:
        """

        info = []
        for i in range(stop):
            loc = (By.XPATH, f'//tr[{i + 1}]//td[2]//label[1]')

            text = self.get_text(loc)
            # 国有土地使用权证，合同编号字段，可能是以逗号分隔的多个编号，这里不去掉逗号
            if option == 2 and i == 8:
                text = text
            elif ',' in text:
                # 金额、面积等，去掉千分符
                text = text.replace(',', '')
            else:
                text = text
            info.append(text)
        return info


class ContractForStateOwnedLand(Certificate):
    """
    国有土地使用权出（受）让合同
    """

    def input_electronic_supervision_number(self, ec_no):
        """
        输入电子监管号

        :param ec_no: 电子监管号
        :type ec_no: str
        :return: 电子监管号
        :rtype: str
        """
        locator = (By.NAME, 'Dzjgh')
        self.send_keys(locator, ec_no)
        return self.get_attribute(locator)

    def input_contract_no(self, num):
        """
        输入合同编号

        :param num: 合同编号
        :type num: str
        :return: 合同编号
        :rtype: str
        """
        return self.send_keys((By.NAME, 'Htbh'), num)

    def select_mode_of_land_acquisition(self, option=2):
        """
        选择土地取得方式

        :param option: 土地取得方式
            1: 请选择
            2: 出让
            3: 转让
            4: 划拨
            5: 其他
        :type option: int
        :return: 土地取得方式
        :rtype: str
        """

        # 土地取得方式 loc
        locator = (By.XPATH, "//input[@placeholder='请选择']")
        self.click(locator)
        if 2 <= option <= 5:
            drop_down_list_xpath = f'//body//dd[{option}]'
        else:
            raise ValueError('请输入正确的参数: 1-5')
        self.click((By.XPATH, drop_down_list_xpath))
        return self.get_attribute(locator)

    def input_transferor(self, name):
        """
        输入出让人

        :param name: 出让人名称
        :type name: str
        :return: 出让人
        :rtype: str
        """

        return self.send_keys((By.NAME, 'Htjf'), name)

    def input_transferee(self, name):
        """
        输入受让人

        :param name: 受让人名称
        :type name: str
        :return: 受让人
        :rtype: str
        """

        return self.send_keys((By.NAME, 'Htyf'), name)

    def input_land_no(self, no):
        """
        输入宗地号

        :param no: 宗地号
        :type no: str
        :return: 宗地号
        :rtype: str
        """

        return self.send_keys((By.NAME, 'Ydpzwh'), no)

    def input_project_location(self, location):
        """
        输入项目位置

        :param location: 项目位置
        :type location: str
        :return: 项目位置
        :rtype: str
        """

        return self.send_keys((By.NAME, 'Xmdz'), location)

    def select_date_of_contract(self, option=1):
        """
        选择合同签订日期，默认当前时间

        :return: 合同签订日期
        :rtype: str
        """
        locator = (By.NAME, 'Htqdrq')
        # self.click(locator)
        # if option == 2 or option == 3:
        #     self.clear_date(locator)
        self.select_date(locator, option)
        return self.get_attribute(locator)

    def select_date_of_land_delivery(self, option=1):
        """
        选择土地交付日期，默认当前时间

        :return: 土地交付日期
        :rtype: str
        """
        locator = (By.NAME, 'tdjfrq')
        # self.click(locator)
        # if option == 2 or option == 3:
        #     self.clear_date(locator)
        self.select_date(locator, option)
        return self.get_attribute(locator)

    def input_land_transferred_area(self, value):
        """
        输入土地出让面积

        :param value: 面积
        :type value: float
        :return: 面积
        :rtype: str
        """

        return self.send_keys((By.NAME, 'Mj'), value)

    def input_sum_price_of_land(self, value):
        """
        输入总地价

        :param value: 地价
        :type value: float
        :return: 地价
        :rtype: str
        """

        return self.send_keys((By.NAME, 'Zdj'), value)

    def input_plot_ratio(self, value):
        """
        输入容积率

        :param value: 容积率
        :type value: float
        :return: 容积率
        :rtype: str
        """

        return self.send_keys((By.NAME, 'Rjl'), value)

    def input_contract_amount(self, value):
        """
        输入合同金额

        :param value: 合同金额
        :type value: float
        :return: 合同金额
        :rtype: str
        """

        return self.send_keys((By.NAME, 'Htje'), value)

    def input_greening_rate(self, greening_rate):
        """输入绿化率"""
        return self.send_keys((By.NAME, 'lhl'), greening_rate)

    def add(self, electronic_supervision_number, contract_no, mode_of_land_acquisition, transferor, transferee,
            land_number, project_location, land_transferred_area, price_of_land, plot_ratiod, contract_amount,
            greening_rate):

        """添加"""

        self.click_data_entry()
        self.select_credentials(1)
        self.click_add_btn()
        info = self.input_basic_info(contract_amount, contract_no, electronic_supervision_number, land_number,
                                     land_transferred_area, mode_of_land_acquisition, plot_ratiod, price_of_land,
                                     project_location, transferee, transferor, greening_rate)
        # 保存
        self.click_save_btn()
        time.sleep(1)
        # 保存后，点击添加附件
        self.click((By.LINK_TEXT, "添加附件"))
        time.sleep(1)
        # 新增时上传附件会弹框提示必须上传**，点击确定按钮
        self.switch_to_upload()
        self.click((By.LINK_TEXT, '确定'))
        # 点击上传附件
        count = self.upload_easy()
        project_id = self.project_id
        info_database = self.get_info(project_id, contract_no)
        if count and info == info_database:
            self.logger.info('[国有土地使用权出(受)让合同]添加成功')
            return 'success'
        elif not count and info == info_database:
            self.logger.error('[国有土地使用权出(受)让合同]添加成功，附件上传失败')
        else:
            self.logger.error('[国有土地使用权出(受)让合同]添加失败')
            self.logger.error('[国有土地使用权出(受)让合同]-前台: ' + str(info))
            self.logger.error('[国有土地使用权出(受)让合同]-后台: ' + str(info_database))
            return 'failure'

    def update(self, electronic_supervision_number, contract_no, mode_of_land_acquisition, transferor, transferee,
               land_number, project_location, land_transferred_area, price_of_land, plot_ratiod, contract_amount,
               greening_rate, date_of_contract, date_of_land_delivery):

        """修改"""

        # 点击数据录入
        self.click_data_entry()
        # 选择证件
        self.select_credentials(1)
        # 点击修改按钮
        self.click_update_btn()
        time.sleep(1)
        # 输入新数据
        info = self.input_basic_info(contract_amount, contract_no, electronic_supervision_number, land_number,
                                     land_transferred_area, mode_of_land_acquisition, plot_ratiod, price_of_land,
                                     project_location, transferee, transferor, greening_rate, date_of_contract,
                                     date_of_land_delivery)
        # 保存
        self.click_save_btn()
        # 获取提示信息
        self.switch_to_default_content()
        flag = self.text_to_be_present_in_text((By.XPATH, '(//div[@class="layui-layer-content"])[last()]'), '修改成功！')
        project_id = self.project_id
        info_database = self.get_info(project_id, contract_no)
        # 如果提示信息为“修改成功！”且前后台合同数据一致，则修改成功
        if flag and info == info_database:
            self.logger.info('[国有土地使用权出(受)让合同]修改成功')
            return 'success'
        else:
            self.logger.error('[国有土地使用权出(受)让合同]修改失败')
            self.logger.error('[国有土地使用权出(受)让合同]-前台: ' + str(info))
            self.logger.error('[国有土地使用权出(受)让合同]-后台: ' + str(info_database))
            return 'failure'

    def detail(self):
        """详情"""

        self.click_btn_of_certificate(1, self.detail_btn)

        # 合同信息
        info = self.get_info_of_detail(stop=14)
        contract_id = info[0]
        # 附件个数
        count = self.get_count_files()
        project_id = self.project_id
        certificate_type = 16
        sql = 'SELECT h.ZsGuid FROM Htqd h WHERE h.Xmid = ? AND h.Htbh = ?'
        count_db = self.get_count_of_annex(certificate_type, self.project_id, contract_id, sql)
        info_db = self.get_info(project_id, contract_id)
        # 数据校验
        certificate_name = '国有土地使用权出(受)让合同'
        info.sort()
        msg = self.check_detail_data(certificate_name, info, info_db, count, count_db)
        return msg

    def delete(self, **kwargs):
        """删除"""

        return super().delete(1, '国有土地使用权出(受)让合同')

    def check_subtotal(self):
        """校验小计数据与列表合计数据是否一致"""

        # 选择证件
        self.click_data_entry()
        self.select_credentials(1)
        time.sleep(1)
        # 合同金额-小计
        contract_amount_subtotal = Decimal(
            self.get_text((By.XPATH, "//span[@class='layui-form-label htje']")).replace(',', ''))
        # 面积-小计
        area_subtotal = Decimal(self.get_text((By.XPATH, "//span[@class='layui-form-label htje2']")).replace(',', ''))
        # 总地价-小计
        total_price_subtotal = Decimal(
            self.get_text((By.XPATH, "//span[@class='layui-form-label htje3']")).replace(',', ''))
        # 获取列表tbody
        contract_amount_list = 0.00
        area_list = 0.00
        total_price_list = 0.00
        # 获取tbody中的所有行
        trs = self.get_tr_of_level_2()
        for tr in trs:
            tds = tr.find_elements(By.TAG_NAME, 'td')
            contract_amount_list = Decimal(contract_amount_list) + Decimal(
                tds[9].find_element(By.TAG_NAME, 'div').text.replace(',', ''))
            area_list = Decimal(area_list) + Decimal(tds[7].find_element(By.TAG_NAME, 'div').text.replace(',', ''))
            total_price_list = Decimal(total_price_list) + Decimal(
                tds[8].find_element(By.TAG_NAME, 'div').text.replace(',', ''))
        msg = 'failure'
        if contract_amount_subtotal == contract_amount_list and area_subtotal == area_list and total_price_subtotal \
                == total_price_list:
            self.logger.info('[国有土地使用权出(受)让合同]-小计数据与列表合计数据一致')
            msg = 'success'
        else:
            self.logger.error('[国有土地使用权出(受)让合同]-小计数据与列表合计数据不一致')
            self.logger.error(
                f'{{"[国有土地使用权出(受)让合同]金额-小计": {contract_amount_subtotal}, "[国有土地使用权出(受)让合同]面积-小计": {area_subtotal}, '
                f'"[国有土地使用权出(受)让合同]总地价-小计": '
                f'{total_price_subtotal}}}')
            self.logger.error(
                f'{{"[国有土地使用权出(受)让合同]金额-列表": {contract_amount_list}, "[国有土地使用权出(受)让合同]面积-列表": {area_list}, '
                f'"[国有土地使用权出(受)让合同]总地价-列表": {total_price_list}}}')
            screenshot.screenshot(self.driver)

        return msg

    def export(self, **kwargs):
        """导出"""

        return super().export(option=1, certificate_name='国有土地使用权出(受)让合同')

    def input_basic_info(self, contract_amount, contract_no, electronic_supervision_number, land_number,
                         land_transferred_area, mode_of_land_acquisition, plot_ratio, price_of_land, project_location,
                         transferee, transferor, greening_rate, date_of_contract=1, date_of_land_delivery=1):
        electronic_supervision_number = self.input_electronic_supervision_number(electronic_supervision_number)
        contract_no = self.input_contract_no(contract_no)
        mode_of_land_acquisition = self.select_mode_of_land_acquisition(mode_of_land_acquisition)
        transferor = self.input_transferor(transferor)
        transferee = self.input_transferee(transferee)
        land_number = self.input_land_no(land_number)
        project_location = self.input_project_location(project_location)
        date_of_contract = self.select_date_of_contract(date_of_contract)
        date_of_land_delivery = self.select_date_of_land_delivery(date_of_land_delivery)
        land_transferred_area = self.input_land_transferred_area(land_transferred_area)
        price_of_land = self.input_sum_price_of_land(price_of_land)
        plot_ratio = self.input_plot_ratio(plot_ratio)
        contract_amount = self.input_contract_amount(contract_amount)
        greening_rate = self.input_greening_rate(greening_rate)
        info = [electronic_supervision_number, contract_no, mode_of_land_acquisition, transferor, transferee,
                land_number, project_location, date_of_contract, date_of_land_delivery, land_transferred_area,
                price_of_land, plot_ratio, contract_amount, greening_rate]
        info.sort()
        return info


class CertificateOfStateOwnedLandUse(Certificate):
    """国有土地使用权证"""

    logger = Logger('CertificateOfStateOwnedLandUse').get_log()

    def click_certificate_of_state_owned_land_use(self):
        """选择证件"""

        self.click_data_entry()
        self.select_credentials(2)

    def add_certificate(self, date_of_issue, certificate_id, usage_type, land_use, land_type, land_id, use_area,
                        use_area_this):
        """
        添加国有土地使用权证

        :param date_of_issue: 发证日期
        :param certificate_id: 证书编号
        :param usage_type: 使用权类型
        :param land_use: 土地用途
        :param land_type: 土地类型
        :param land_id: 地号
        :param use_area: 使用权面积
        :param use_area_this: 本项目使用权面积
        :return:
        """

        self.click_certificate_of_state_owned_land_use()
        self.click_add_btn()

        info = self.input_info(date_of_issue, certificate_id, usage_type, land_use, land_type, land_id, use_area,
                               use_area_this)
        # 点击保存按钮
        self.click((By.XPATH, '//div[@class="bottomC"]/a[1]'))
        # 点击添加附件
        self.click((By.LINK_TEXT, '添加附件'))
        count = self.upload_easy()

        # 数据库证件信息
        project_id = self.project_id
        info_table = self.get_certificate_info(project_id, certificate_id)
        msg = 'failure'
        if count and info == info_table:
            self.logger.info('[国有土地使用权证]添加成功')
            msg = 'success'
        else:
            self.logger.error('[国有土地使用权证]添加失败')
            self.logger.error(f'[国有土地使用权证]附件个数：{count}')
            self.logger.error(f'[国有土地使用权证]-前台：{info}')
            self.logger.error(f'[国有土地使用权证]-后台：{info_table}')
        return msg

    def update_certificate(self, date_of_issue, certificate_id, usage_type, land_use, land_type, land_id, use_area,
                           use_area_this):
        """修改国有土地权证"""

        self.click_certificate_of_state_owned_land_use()
        self.click_update_btn()
        time.sleep(1)
        info = self.input_info(date_of_issue, certificate_id, usage_type, land_use, land_type, land_id, use_area,
                               use_area_this)
        # 点击保存按钮
        self.click((By.XPATH, '//div[@class="bottomC"]/a[1]'))
        loc_msg = (By.XPATH, '(//div[@class="layui-layer-content"])[last()]')
        self.switch_to_default_content()
        is_present = self.text_to_be_present_in_text(loc_msg, '修改成功！')
        msg = 'failure'
        # 数据库证件信息
        project_id = self.project_id
        info_table = self.get_certificate_info(project_id, certificate_id)
        if is_present and info == info_table:
            self.logger.info('[国有土地使用权证]修改成功')
            msg = 'success'
        else:
            self.logger.error('[国有土地使用权证]修改失败')
            self.logger.error(f'[国有土地使用权证]-前台：{info}')
            self.logger.error(f'[国有土地使用权证]-后台：{info_table}')
        return msg

    def detail(self):
        """国有土地使用权证：查看详情"""

        self.click_btn_of_certificate(2, self.detail_btn)  # 点击详情按钮

        # 页面数据
        info = self.get_info_of_detail(stop=10, option=2)
        # 证书编号
        certificate_id = info[1]
        # db数据
        info_db = self.get_certificate_info(self.project_id, certificate_id)
        # 页面附件个数
        count_annex = self.get_count_files()
        certificate_type = 1  # 国有土地使用权证
        # db附件个数
        sql = 'SELECT a.ZsGuid FROM DJgytdsyz a WHERE a.Xmid = ? AND a.Zsbh = ?'
        count_annex_db = self.get_count_of_annex(certificate_type, self.project_id, certificate_id, sql)
        # 前后台数据校验
        certificate_name = '国有土地使用权证'
        info.sort()
        msg = self.check_detail_data(certificate_name, info, info_db, count_annex, count_annex_db)
        return msg

    def delete(self, **kwargs):
        """删除国有土地使用权证"""

        return super().delete(2, '国有土地使用权证')

    def export(self, **kwargs):
        """导出"""

        return super().export(option=2, certificate_name='国有土地使用权证')

    def check_subtotal(self):
        """校验小计数据与列表合计数据是否一致"""

        # 选择证件
        self.click_data_entry()
        self.select_credentials(2)
        time.sleep(1)
        # 使用权面积-小计
        area_use = float(
            self.get_text((By.XPATH, "//span[@class='layui-form-label htje']")).replace(',', ''))
        # 本期项目使用权面积-小计
        current_area_subtotal = float(self.get_text((By.XPATH, "//span[@class='layui-form-label htje2']")).replace(',',
                                                                                                                   ''))
        # 获取列表tbody
        area_use_list = 0
        current_area_subtotal_list = 0
        # 获取tbody中的所有行
        trs = self.get_tr_of_level_2()
        for tr in trs:
            tds = tr.find_elements(By.TAG_NAME, 'td')
            area_use_list += float(tds[3].find_element(By.TAG_NAME, 'div').text.replace(',', ''))
            current_area_subtotal_list += float(tds[4].find_element(By.TAG_NAME, 'div').text.replace(',', ''))
        msg = 'failure'
        if area_use == area_use_list and current_area_subtotal == current_area_subtotal_list:
            self.logger.info('[国有土地使用权证]-小计数据与列表合计数据一致')
            msg = 'success'
        else:
            self.logger.error('[国有土地使用权证]-小计数据与列表合计数据不一致')
            self.logger.info(
                f'{{"[国有土地使用权证]使用权面积-小计": {area_use}, "[国有土地使用权证]本期项目使用权面积-小计": {current_area_subtotal}}}')
            self.logger.info(
                f'{{"[国有土地使用权证]使用权面积-列表": {area_use_list}, "[国有土地使用权证]本期项目使用权面积-列表": {current_area_subtotal_list}}}')
            screenshot.screenshot(self.driver)

        return msg

    def input_info(self, date_of_issue, certificate_id, usage_type, land_use, land_type, land_id, use_area,
                   use_area_this):
        """输入国有土地使用权证各字段"""

        # 输入发证日期
        loc_date_of_issue = (By.NAME, 'Fzrq')
        self.send_keys(loc_date_of_issue, date_of_issue)
        date_of_issue = self.get_attribute(loc_date_of_issue)
        # 输入证书编号
        loc_certificate_id = (By.NAME, 'Zsbh')
        certificate_id = self.send_keys(loc_certificate_id, certificate_id)
        # 选择使用权类型：出让
        loc_usage_type = (By.XPATH, '(//div[@class="layui-select-title"])[1]/input[1]')
        self.click(loc_usage_type)  # 点击下拉
        self.click(
            (By.XPATH, f'(//div[@class="layui-select-title"])[1]/following-sibling::dl[1]/dd[{usage_type}]'))  # 选择 出让
        usage_type = self.get_attribute(loc_usage_type)
        # 选择土地用途：住宅
        loc_land_use = (By.XPATH, '(//div[@class="layui-select-title"])[2]/input[1]')
        self.click(loc_land_use)
        self.click((By.XPATH, f'(//div[@class="layui-select-title"])[2]/following-sibling::dl[1]/dd[{land_use}]'))
        land_use = self.get_attribute(loc_land_use)
        # 选择土地类型：耕地
        loc_land_type = (By.XPATH, '(//div[@class="layui-select-title"])[3]/input[1]')
        self.click(loc_land_type)
        self.click((By.XPATH, f'(//div[@class="layui-select-title"])[3]/following-sibling::dl[1]/dd[{land_type}]'))
        land_type = self.get_attribute(loc_land_type)
        # 输入地号
        land_id = self.send_keys((By.NAME, 'Dh'), land_id)
        # 输入使用权面积
        use_area = self.send_keys((By.NAME, 'Syqmj'), use_area)
        # 输入本项目使用权面积
        use_area_this = self.send_keys((By.NAME, 'Bqxmsyqmj'), use_area_this)
        # 选择合同编号：全选
        loc_contract_id = (By.XPATH, "//div[@class='xm-input xm-select']")
        self.click(loc_contract_id)
        time.sleep(1)
        self.click((By.XPATH, "//div[@class='xm-cz-group']//div[1]//span[1]"))
        time.sleep(1)
        contract_id = self.get_attribute((By.XPATH, '//div[@class="xm-select-label"]'), 'innerText').replace('\n', ',')
        # 获取合同宗地面积
        contract_parcel_area = self.get_attribute((By.XPATH, "//input[@placeholder='合同宗地面积']"))

        # 页面证件信息
        info = [date_of_issue, certificate_id, usage_type, land_use, land_type, land_id, use_area, use_area_this,
                contract_id, contract_parcel_area]
        info.sort()
        return info

    def get_certificate_info(self, project_id, certificate_id):
        """根据项目id、证书编号查询证书信息"""

        conn = self.get_conn()
        cursor = conn.cursor()
        # 合同编号
        with conn:
            cursor.execute('SELECT t.Ht_Guid FROM DJgytdsyz t WHERE t.Xmid = ? AND t.Zsbh = ?', project_id,
                           certificate_id)
            row = cursor.fetchone()
            row = row[0].split(';')
        id_list = []
        contract_parcel_area = 0
        for item in row:
            with conn:
                cursor.execute('SELECT t.Htbh FROM Htqd t WHERE t.Xmid = ? AND t.ZsGuid = ?', project_id, item)
                row = cursor.fetchone()
            id_list.append(row[0])
            # 合同宗地面积
            with conn:
                cursor.execute('SELECT convert(DECIMAL(18, 2), t.Mj) FROM Htqd t WHERE t.Htbh = ?', row[0])
                row = cursor.fetchone()
                contract_parcel_area += row[0]
        ids = ','.join(id_list)

        # 合同信息
        with conn:
            cursor.execute("""
                                SELECT t.Fzrq,
                                       t.Zsbh,
                                       (SELECT a.MC FROM Zd_Tdqdfs a WHERE a.DM = t.Qdfs),
                                       t.Tdyt,
                                       (SELECT b.LX_MC FROM ZDtdlx b WHERE b.LX_DM = t.Tdlx),
                                       t.Dh,
                                       CONVERT(DECIMAL(18, 2), t.Syqmj),
                                       CONVERT(DECIMAL(18, 2), t.Bqxmsyqmj)
                                FROM DJgytdsyz t
                                WHERE t.Xmid = ?
                                  AND t.Zsbh = ?
                            """, project_id, certificate_id)
            row = list(cursor.fetchone())
            row[6] = str(row[6])
            row[7] = str(row[7])
            row.append(ids)
            row.append(str(contract_parcel_area))
            row.sort()
        return row
